// 修复 parent_recurring_id 外键约束错误
const { Pool } = require('pg')

const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'aide',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD || 'unidt@123'
})

async function fixParentRecurringConstraint() {
  try {
    console.log('🔍 检查 parent_recurring_id 外键约束问题...\n')
    
    // 1. 检查有问题的记录
    const badRecords = await pool.query(`
      SELECT t1.id, t1.title, t1.parent_recurring_id, t1.is_recurring
      FROM tasks t1
      LEFT JOIN tasks t2 ON t1.parent_recurring_id = t2.id
      WHERE t1.parent_recurring_id IS NOT NULL 
        AND t2.id IS NULL
    `)
    
    console.log(`📊 发现 ${badRecords.rows.length} 条有问题的记录:`)
    
    if (badRecords.rows.length > 0) {
      badRecords.rows.forEach((row, index) => {
        console.log(`${index + 1}. 任务: ${row.title}`)
        console.log(`   ID: ${row.id}`)
        console.log(`   无效的父ID: ${row.parent_recurring_id}`)
        console.log(`   是重复任务: ${row.is_recurring}`)
        console.log('')
      })
      
      // 2. 提供修复选项
      console.log('🔧 修复选项:')
      console.log('1. 清除无效的 parent_recurring_id (推荐)')
      console.log('2. 删除这些有问题的记录')
      console.log('')
      
      // 自动选择选项1 - 清除无效的 parent_recurring_id
      console.log('✅ 执行修复选项 1: 清除无效的 parent_recurring_id...')
      
      const updateResult = await pool.query(`
        UPDATE tasks 
        SET parent_recurring_id = NULL
        WHERE parent_recurring_id IS NOT NULL 
          AND parent_recurring_id NOT IN (SELECT id FROM tasks)
      `)
      
      console.log(`✅ 修复完成! 更新了 ${updateResult.rowCount} 条记录`)
      
      // 3. 验证修复结果
      const verifyResult = await pool.query(`
        SELECT COUNT(*) as count
        FROM tasks t1
        LEFT JOIN tasks t2 ON t1.parent_recurring_id = t2.id
        WHERE t1.parent_recurring_id IS NOT NULL 
          AND t2.id IS NULL
      `)
      
      console.log(`🔍 验证结果: 还有 ${verifyResult.rows[0].count} 条有问题的记录`)
      
    } else {
      console.log('✅ 没有发现外键约束问题!')
    }
    
    // 4. 显示当前重复任务统计
    const statsResult = await pool.query(`
      SELECT 
        COUNT(*) as total_tasks,
        COUNT(CASE WHEN is_recurring = true THEN 1 END) as recurring_tasks,
        COUNT(CASE WHEN parent_recurring_id IS NOT NULL THEN 1 END) as tasks_with_parent
      FROM tasks
    `)
    
    const stats = statsResult.rows[0]
    console.log('\n📊 当前任务统计:')
    console.log(`   总任务数: ${stats.total_tasks}`)
    console.log(`   重复任务数: ${stats.recurring_tasks}`)
    console.log(`   有父任务的实例: ${stats.tasks_with_parent}`)
    
  } catch (error) {
    console.error('❌ 修复失败:', error.message)
    if (error.code === '23503') {
      console.error('这是外键约束错误，请运行此脚本修复')
    }
  } finally {
    await pool.end()
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  fixParentRecurringConstraint()
}

module.exports = { fixParentRecurringConstraint }
